package com.basis.handler;

import org.apache.log4j.Logger;
import org.codehaus.jettison.json.JSONArray;
import org.codehaus.jettison.json.JSONObject;
import org.hibernate.Query;
import org.openbravo.client.kernel.BaseActionHandler;
import org.openbravo.dal.core.OBContext;
import org.openbravo.dal.service.OBDal;
import org.openbravo.database.SessionInfo;
import org.openbravo.model.ad.access.User;
import org.openbravo.model.ad.access.UserRoles;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * 报警统计 维保、维修、使用次数、使用寿命、点检
 * @author xgh
 */
@SuppressWarnings("unchecked")
public class MessageAlertStatisticsHandler extends BaseActionHandler {
	private Logger logger = Logger.getLogger(this.getClass());
	private static SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm");
	@Override
	protected JSONObject execute(Map<String, Object> parameters, String data) {
		JSONObject result = new JSONObject();
		try {
			OBContext.setAdminMode();
			User user = OBDal.getInstance().get(User.class, SessionInfo.getUserId());
			List<UserRoles> roles = user.getADUserRolesList();
			String roleIds = "";
			for(UserRoles ur:roles){
				roleIds+="'"+ur.getId()+"',";
			}
			if(!roleIds.equals("")){
				roleIds = roleIds.substring(0,roleIds.length()-1);
			}
			String checkUserWSql = "select aw.ad_window_id,aw.name from ad_window_access awa,ad_window aw "+
									"where 1=1 "+
									"and awa.ad_window_id=aw.ad_window_id "+
									"and awa.ad_role_id in (select aur.ad_role_id from ad_user_roles aur,ad_role ar "+
									"where 1=1 "+
									"and aur.ad_role_id=ar.ad_role_id "+
									"and aur.ad_user_roles_id in  "+
									"("+roleIds+")) "+
									"and instr(aw.name,'设备')>0 "+
									"group by aw.ad_window_id,aw.name";
//			System.out.println(checkUserWSql);
			Query checkUserquery = OBDal.getInstance().getSession().createSQLQuery(checkUserWSql);
			checkUserquery.setFetchSize(10);
			checkUserquery.setFirstResult(0);
			if(checkUserquery==null||checkUserquery.list()==null||checkUserquery.list().size()==0){
				result.put("success", false);
				return result;
			}
			
			String sql = 
					"select totalData.*,am.name menuName,am.ad_window_id openWindowId from ("+
					" select count(1) allCount,"+
					" count(case when (emp.status!='OK' and emp.plan_state_time>=now() and emp.prewarning_time is not null " +
							"and (emp.plan_state_time-now())*24<=emp.prewarning_time) then 1 else null end) warnCount,"+
					" count(case when (emp.status!='OK' and emp.plan_state_time<now()) then 1 else null end) expireCount,"+
					" 'eqMaintainWarn' msgType,'maintainPlanWarn' msgObjType,1 seq from Eam_Maintain_plan emp"+
					" where 1=1"+
					" and emp.distinguish='MAINTAIN'"+
					" union all"+
					" select count(1) eqRepairWarnCount,"+
					" count(case when (emp.status!='OK' and (now()-emp.created)*24>=12) then 1 else null end) warnCount,"+
					" count(case when (emp.status!='OK' and (now()-emp.created)*24>=24) then 1 else null end) expireCount,"+
					" 'eqRepairWarn' msgType,'repairPlanWarn' msgObjType,2 seq from Eam_Maintain_plan emp"+
					" where 1=1"+
					" and emp.distinguish='REPAIR'"+
					" union all"+
					" select count(1) eqCheckWarnCount,count(case when (efrl.state!='OK' and efrl.state!='CANCEL' " +
							"and efrl.Next_Cycle_Time is not null) then 1 else null end),0,'eqCheckWarn' msgType,'checkPlanWarn' msgObjType," +
					"3 seq from Eam_Check_Plan efrl"+
					" union all"+
					" select count(1) eqUseWarnCount,0,0,'eqUseWarn' msgType,'equipmentUseCount' msgObjType,4 seq from Eam_Facilities ef"+
					" where ef.limit_time is not null"+
					" and ef.limit_time_to is not null"+
					" and ef.limit_use_count is not null"+
					" and (ef.limit_time-ef.limit_use_count)>=0"+
					" and (ef.limit_use_count-ef.limit_time_to)>=0"+
					" union all"+
					" select count(1) eqLifeWarnCount,0,0,'eqLifeWarn' msgType,'equipmentLifeWarn' msgObjType,5 seq from Eam_Facilities ef"+
					" where ef.start_date is not null"+
					" and ef.limit_date is not null"+
					" and ef.limit_date_to is not null"+
					" and (ef.limit_date-(now()-ef.start_date)*24)>=0"+
					" and (ef.limit_date-(now()-ef.start_date)*24)<=ef.limit_date_to"+
					" ) totalData"+ 
					" left join emes_message_obj emo on totalData.msgObjType= emo.code"+
					" left join ad_menu am on emo.ad_menu_id=am.ad_menu_id"+
					" order by totalData.seq"
					;
			//Query query = OBDal.getInstance().getSession().createSQLQuery(sql);
			//query.setString(0, user.getId());
			
			//query.setFetchSize(10);
			//query.setFirstResult(0);
			List<Object[]> list = new ArrayList();//query.list();
			
			JSONArray array = new JSONArray();
			JSONObject row = null;
			
			for (Object[] arr : list) {
				row = new JSONObject();
				row.put("allCount", arr[0]);
				row.put("warnCount", arr[1]);
				row.put("expireCount", arr[2]);
				row.put("msgType", arr[3]);
				row.put("msgObjType", arr[4]);
				row.put("seq", arr[5]);
				row.put("menuName", arr[6]);
				row.put("openWindowId", arr[7]);
				row.put("date",dateFormat.format(new Date()));
				array.put(row);
			}
			OBDal.getInstance().commitAndClose();
			result.put("list", array);
			result.put("success", true);
		} catch (Exception e) {
			OBDal.getInstance().rollbackAndClose();
			logger.error("获取消息提醒内容异常：" + e.getMessage(), e);
			try {
				result.put("success", false);
				result.put("msg", "查询用户消息提醒内容异常");
			} catch (Exception e1) {
			}
		} finally {
			OBContext.restorePreviousMode();
		}
		return result;
	}
}
